﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;

namespace 数据库操作
{
    internal class Program
    {

        static void test()
        {
            string connStr = "server=localhost;user=yejing;password=123456;database=test;";
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    Console.WriteLine("连接成功！");
                    // 这里可以执行SQL操作

                    // 构造要插入的数据
                    TUser user = new TUser
                    {
                        Name = "张三",
                        Age = 25,
                        Mail = "zhangsan@example.com",
                        Sex = "男",
                        Addr = "北京市",
                        Phone = "13800000000"
                    };

                    // 插入SQL语句
                    string sql = "INSERT INTO tuser (name, age, mail, sex, addr, phone) VALUES (@name, @age, @mail, @sex, @addr, @phone)";
                    using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddWithValue("@name", user.Name);
                        cmd.Parameters.AddWithValue("@age", user.Age);
                        cmd.Parameters.AddWithValue("@mail", user.Mail);
                        cmd.Parameters.AddWithValue("@sex", user.Sex);
                        cmd.Parameters.AddWithValue("@addr", user.Addr);
                        cmd.Parameters.AddWithValue("@phone", user.Phone);

                        int rows = cmd.ExecuteNonQuery();
                        Console.WriteLine($"插入成功，受影响行数: {rows}");
                    }


                    // 查询tuser表数据
                    sql = "SELECT id, name, age, mail, sex, addr, phone FROM tuser";
                    using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine(
                                $"Id: {reader["id"]}, Name: {reader["name"]}, Age: {reader["age"]}, Mail: {reader["mail"]}, Sex: {reader["sex"]}, Addr: {reader["addr"]}, Phone: {reader["phone"]}"
                            );
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("连接失败: " + ex.Message);
                }
            }
        }

        static void Main(string[] args)
        {
            test();
            //string connStr = "server=localhost;user=yejing;password=123456;database=test;";
            //DbHelper helper = new DbHelper(connStr);
            //List<TUser> list = helper.GetAllUsers();
            //// 遍历输出所有用户信息
            //foreach (var item in list)
            //{
            //    Console.WriteLine($"Id: {item.Id}, Name: {item.Name}, Age: {item.Age}, " +
            //        $"Mail: {item.Mail}, Sex: {item.Sex}, " +
            //        $"Addr: {item.Addr}, Phone: {item.Phone}");
            //}

            //string connStr = "server=localhost;user=yejing;password=123456;database=test;";
            //DbHelper helper = new DbHelper(connStr);

            //var users = new List<TUser>
            //{
            //    new TUser { Name = "李四", Age = 30, Mail = "lisi@example.com", Sex = "男", Addr = "上海市", Phone = "13900000001" },
            //    new TUser { Name = "王五", Age = 28, Mail = "wangwu@example.com", Sex = "女", Addr = "广州市", Phone = "13900000002" }
            //};

            //int rows = helper.InsertUsers(users);
            //Console.WriteLine($"批量插入成功，受影响行数: {rows}");

            //var repo = new GenericRepository<TUser>(connStr, "tuser");

            // 插入
            //repo.Insert(new TUser { Name = "张三", Age = 25, Mail = "zhangsan@example.com", Sex = "男", Addr = "北京", Phone = "13800000000" });

            // 更新
            //var user = new TUser { Id = 1, Name = "李四", Age = 30, Mail = "lisi@example.com", Sex = "男", Addr = "上海", Phone = "13900000001" };
            //repo.Update(user);

            // 删除
            //repo.DeleteById(1);

            // 查询所有
            //List<TUser> users = repo.GetAll();
            //Console.WriteLine(users.Count);
            //foreach (var item in users)
            //{
            //    Console.WriteLine($"Id: {item.Id}, Name: {item.Name}, Age: {item.Age}, " +
            //        $"Mail: {item.Mail}"+$"Addr:{item.Addr}");
            //}


            //var users = new List<TUser>
            //{
            //    new TUser { Name = "李四", Age = 30, Mail = "lisi@example.com", Sex = "男", Addr = "上海市", Phone = "13900000001" },
            //    new TUser { Name = "王五", Age = 28, Mail = "wangwu@example.com", Sex = "女", Addr = "广州市", Phone = "13900000002" }
            //};
            //int rows = helper.InsertUsersByDataSet(users);
            //Console.WriteLine($"批量插入成功，受影响行数: {rows}");


            //DbHelper helper = new DbHelper(connStr);
            //DataTable table = helper.GetAllUsersByDataSet();
            //foreach (DataRow row in table.Rows)
            //{
            //    Console.WriteLine($"Id: {row["id"]}, Name: {row["name"]}, Age: {row["age"]}, Mail: {row["mail"]}, Sex: {row["sex"]}, Addr: {row["addr"]}, Phone: {row["phone"]}");
            //}

            //DataTable table = new DataTable();
            //table.Columns.Add("Id", typeof(int));
            //table.Columns.Add("Name", typeof(string));
            //table.Rows.Add(1, "张三");
            //table.Rows.Add(2, "李四");

            //foreach (DataRow row in table.Rows)
            //{
            //    Console.WriteLine($"{row["Id"]}, {row["Name"]}");
            //}
        }

    }

    public class TUser
    {
        private int _id;
        private string _name;
        private int _age;
        private string _mail;
        private string _sex;
        private string _addr;
        private string _phone;

        public int Id { get => _id; set => _id = value; }
        public string Name { get => _name; set => _name = value; }
        public int Age { get => _age; set => _age = value; }
        public string Mail { get => _mail; set => _mail = value; }
        public string Sex { get => _sex; set => _sex = value; }
        public string Addr { get => _addr; set => _addr = value; }
        public string Phone { get => _phone; set => _phone = value; }

    }



    public class DbHelper
    {
        private readonly string _connStr;

        public DbHelper(string connStr)
        {
            _connStr = connStr;
        }

        public int InsertUser(TUser user)
        {
            using (MySqlConnection conn = new MySqlConnection(_connStr))
            {
                conn.Open();
                string sql = "INSERT INTO tuser (name, age, mail, sex, addr, phone) VALUES (@name, @age, @mail, @sex, @addr, @phone)";
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@name", user.Name);
                    cmd.Parameters.AddWithValue("@age", user.Age);
                    cmd.Parameters.AddWithValue("@mail", user.Mail);
                    cmd.Parameters.AddWithValue("@sex", user.Sex);
                    cmd.Parameters.AddWithValue("@addr", user.Addr);
                    cmd.Parameters.AddWithValue("@phone", user.Phone);

                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public List<TUser> GetAllUsers()
        {
            var users = new List<TUser>();
            using (MySqlConnection conn = new MySqlConnection(_connStr))
            {
                conn.Open();
                string sql = "SELECT id, name, age, mail, sex, addr, phone FROM tuser";
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        users.Add(new TUser
                        {
                            Id = reader.GetInt32("id"),
                            Name = reader.GetString("name"),
                            Age = reader.GetInt32("age"),
                            Mail = reader.GetString("mail"),
                            Sex = reader.GetString("sex"),
                            Addr = reader.GetString("addr"),
                            Phone = reader.GetString("phone")
                        });
                    }
                }
            }
            return users;
        }

        // 更新用户信息
        public int UpdateUser(TUser user)
        {
            using (MySqlConnection conn = new MySqlConnection(_connStr))
            {
                conn.Open();
                string sql = "UPDATE tuser SET name=@name, age=@age, mail=@mail, sex=@sex, addr=@addr, phone=@phone WHERE id=@id";
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@name", user.Name);
                    cmd.Parameters.AddWithValue("@age", user.Age);
                    cmd.Parameters.AddWithValue("@mail", user.Mail);
                    cmd.Parameters.AddWithValue("@sex", user.Sex);
                    cmd.Parameters.AddWithValue("@addr", user.Addr);
                    cmd.Parameters.AddWithValue("@phone", user.Phone);
                    cmd.Parameters.AddWithValue("@id", user.Id);

                    return cmd.ExecuteNonQuery();
                }
            }
        }

        // 删除用户
        public int DeleteUser(int id)
        {
            using (MySqlConnection conn = new MySqlConnection(_connStr))
            {
                conn.Open();
                string sql = "DELETE FROM tuser WHERE id=@id";
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@id", id);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        // 按条件查询（如按姓名）
        public List<TUser> GetUsersByName(string name)
        {
            var users = new List<TUser>();
            using (MySqlConnection conn = new MySqlConnection(_connStr))
            {
                conn.Open();
                string sql = "SELECT id, name, age, mail, sex, addr, phone FROM tuser WHERE name=@name";
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@name", name);
                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            users.Add(new TUser
                            {
                                Id = reader.GetInt32("id"),
                                Name = reader.GetString("name"),
                                Age = reader.GetInt32("age"),
                                Mail = reader.GetString("mail"),
                                Sex = reader.GetString("sex"),
                                Addr = reader.GetString("addr"),
                                Phone = reader.GetString("phone")
                            });
                        }
                    }
                }
            }
            return users;
        }


        public int InsertUsers(List<TUser> users)
        {
            using (MySqlConnection conn = new MySqlConnection(_connStr))
            {
                conn.Open();
                using (var tran = conn.BeginTransaction())
                {
                    try
                    {
                        string sql = "INSERT INTO tuser (name, age, mail, sex, addr, phone) VALUES (@name, @age, @mail, @sex, @addr, @phone)";
                        using (MySqlCommand cmd = new MySqlCommand(sql, conn, tran))
                        {
                            int total = 0;
                            foreach (var user in users)
                            {
                                cmd.Parameters.Clear();
                                cmd.Parameters.AddWithValue("@name", user.Name);
                                cmd.Parameters.AddWithValue("@age", user.Age);
                                cmd.Parameters.AddWithValue("@mail", user.Mail);
                                cmd.Parameters.AddWithValue("@sex", user.Sex);
                                cmd.Parameters.AddWithValue("@addr", user.Addr);
                                cmd.Parameters.AddWithValue("@phone", user.Phone);

                                total += cmd.ExecuteNonQuery();
                            }
                            tran.Commit();
                            return total;
                        }
                    }
                    catch
                    {
                        tran.Rollback();
                        throw;
                    }
                }
            }
        }

        public int InsertUsersByDataSet(List<TUser> users)
        {
            using (MySqlConnection conn = new MySqlConnection(_connStr))
            {
                conn.Open();
                using (var tran = conn.BeginTransaction())
                {
                    try
                    {
                        string sql = "SELECT id, name, age, mail, sex, addr, phone FROM tuser WHERE 1=0"; // 只取结构
                        using (MySqlDataAdapter adapter = new MySqlDataAdapter(sql, conn))
                        {
                            adapter.SelectCommand.Transaction = tran;
                            MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);

                            DataSet ds = new DataSet();
                            adapter.Fill(ds, "tuser");
                            DataTable table = ds.Tables["tuser"];

                            foreach (var user in users)
                            {
                                DataRow row = table.NewRow();
                                // row["id"] 不需要赋值（自增）
                                row["name"] = user.Name;
                                row["age"] = user.Age;
                                row["mail"] = user.Mail;
                                row["sex"] = user.Sex;
                                row["addr"] = user.Addr;
                                row["phone"] = user.Phone;
                                table.Rows.Add(row);
                            }

                            int rows = adapter.Update(ds, "tuser");
                            tran.Commit();
                            return rows;
                        }
                    }
                    catch
                    {
                        tran.Rollback();
                        throw;
                    }
                }
            }
        }

        public DataTable GetAllUsersByDataSet()
        {
            using (MySqlConnection conn = new MySqlConnection(_connStr))
            {
                conn.Open();
                string sql = "SELECT id, name, age, mail, sex, addr, phone FROM tuser";
                using (MySqlDataAdapter adapter = new MySqlDataAdapter(sql, conn))
                {
                    DataSet ds = new DataSet();
                    adapter.Fill(ds, "tuser");
                    return ds.Tables["tuser"];
                }
            }
        }
    }

    public class GenericRepository<T> where T : class, new()
    {
        private readonly string _connStr;
        private readonly string _tableName;
        private readonly PropertyInfo[] _properties;

        public GenericRepository(string connStr, string tableName)
        {
            _connStr = connStr;
            _tableName = tableName;
            _properties = typeof(T).GetProperties();
        }

        // 插入
        public int Insert(T entity)
        {
            var columns = _properties.Where(p => p.Name.ToLower() != "id").Select(p => p.Name).ToArray();
            var values = columns.Select(c => "@" + c).ToArray();
            string sql = $"INSERT INTO {_tableName} ({string.Join(",", columns)}) VALUES ({string.Join(",", values)})";

            using (var conn = new MySqlConnection(_connStr))
            {
                conn.Open();
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    foreach (var col in columns)
                    {
                        var value = typeof(T).GetProperty(col).GetValue(entity) ?? DBNull.Value;
                        cmd.Parameters.AddWithValue("@" + col, value);
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        // 删除
        public int DeleteById(object id)
        {
            string sql = $"DELETE FROM {_tableName} WHERE id=@id";
            using (var conn = new MySqlConnection(_connStr))
            {
                conn.Open();
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@id", id);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        // 更新
        public int Update(T entity)
        {
            var columns = _properties.Where(p => p.Name.ToLower() != "id").Select(p => p.Name).ToArray();
            string setClause = string.Join(",", columns.Select(c => $"{c}=@{c}"));
            string sql = $"UPDATE {_tableName} SET {setClause} WHERE id=@id";

            using (var conn = new MySqlConnection(_connStr))
            {
                conn.Open();
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    foreach (var col in columns)
                    {
                        var value = typeof(T).GetProperty(col).GetValue(entity) ?? DBNull.Value;
                        cmd.Parameters.AddWithValue("@" + col, value);
                    }
                    // 假设主键为Id
                    var idValue = typeof(T).GetProperty("Id").GetValue(entity);
                    cmd.Parameters.AddWithValue("@id", idValue);
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        // 查询所有
        public List<T> GetAll()
        {
            var list = new List<T>();
            using (var conn = new MySqlConnection(_connStr))
            {
                conn.Open();
                string sql = $"SELECT * FROM {_tableName}";
                using (var cmd = new MySqlCommand(sql, conn))
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        T entity = new T();
                        foreach (var prop in _properties)
                        {
                            if (!reader.HasColumn(prop.Name) || reader[prop.Name] is DBNull) continue;
                            var value = reader[prop.Name];
                            // 类型不一致时自动转换
                            if (value != null && prop.PropertyType != value.GetType())
                            {
                                value = Convert.ChangeType(value, prop.PropertyType);
                            }
                            prop.SetValue(entity, value);
                        }
                        list.Add(entity);
                    }
                }
            }
            return list;
        }
    }

    /// <summary>
    /// 扩展方法，用于检查 IDataRecord 是否包含指定列
    /// </summary>
    public static class DataReaderExtensions
    {
        public static bool HasColumn(this IDataRecord reader, string columnName)
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                if (reader.GetName(i).Equals(columnName, StringComparison.OrdinalIgnoreCase))
                    return true;
            }
            return false;
        }
    }
}
